
package com.bosicen.crm.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.impl.CriteriaImpl;
import org.hibernate.transform.ResultTransformer;
import org.springframework.util.Assert;

import com.bosicen.crm.tools.*;
import com.bosicen.crm.tools.PropertyFilter.MatchType;






/**
 * 封装SpringSide扩展功能的Hibernat DAO泛型基类.
 * 
 * 扩展功能包括分页查询,按属性过滤条件列表查询.
 * 可在Service层直接使用,也可以扩展泛型DAO子类使用,见两个构造函数的注释.
 * 
 * @param <T> DAO操作的对象类型
 * @param <PK> 主键类型
 * 
 */
public class HibernateDao<T, PK extends Serializable> extends BaseHibernateDao<T, PK> {
	private static final Log log = LogFactory.getLog(HibernateDao.class);
	/**
	 * 用于Dao层子类使用的构造函数.
	 * 通过子类的泛型定义取得对象类型Class.
	 * eg.
	 * public class UserDao extends HibernateDao<User, Long>{
	 * }
	 */
	public HibernateDao() {
		super();
	}

	/**
	 * 用于省略Dao层, Service层直接使用通用HibernateDao的构造函数.
	 * 在构造函数中定义对象类型Class.
	 * eg.
	 * HibernateDao<User, Long> userDao = new HibernateDao<User, Long>(sessionFactory, User.class);
	 */
	public HibernateDao(final SessionFactory sessionFactory, final Class<T> entityClass) {
		super(sessionFactory, entityClass);
	}

	//-- 分页查询函数 --//
	/**
	 * 分页获取全部对象.
	 
	public Page<T> getAll(final Page<T> page) {
		return findPage(page);
	}
*/
	/**
	 * 按HQL分页查询.
	 * 
	 * @param page 分页参数.不支持其中的orderBy参数.
	 * @param hql hql语句.
	 * @param values 数量可变的查询参数,按顺序绑定. 数组方式
	 * 
	 * @return 分页查询结果, 附带结果列表及所有查询时的参数.
	 */
	@SuppressWarnings("unchecked")
	public Page findPage( Page page, final String hql, final Object... values) {
		Assert.notNull(page, "page不能为空");
		Page p=null;
	    try{
			long totalCount = countHqlResult(hql, values);
			if(totalCount>0){
				p= PageUtil.createPage(page, AppUtil.ObjectToInt(totalCount));
				Query q = createQuery(hql, values);
				setPageParameter(q, p);
				List result = q.list();
				p.setResult(result);
			}
	    }catch(Exception ex){
	    	ex.printStackTrace();
	    }
		return p;
	}

	/**
	 * 按HQL分页查询.
	 * 
	 * @param page 分页参数.
	 * @param hql hql语句.
	 * @param values 命名参数,按名称绑定.  Map 方式
	 * 
	 * @return 分页查询结果, 附带结果列表及所有查询时的参数.  
	 */
	@SuppressWarnings("unchecked")
	public Page findPage( Page page, final String hql, final Map<String, ?> values) {
		Assert.notNull(page, "page不能为空");
		Page p=null;
		try{

			long totalCount = countHqlResult(hql, values);
			if(totalCount>0){
				p= PageUtil.createPage(page, AppUtil.ObjectToInt(totalCount));	
				Query q = createQuery(hql, values);
				setPageParameter(q, p);
		
				List result = q.list();
				p.setResult(result);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return p;
	}


	/**
	 * 设置分页参数到Query对象,辅助函数.
	 */
	protected Query setPageParameter(final Query q,  Page page) {
		q.setFirstResult(page.getBeginIndex());
		q.setMaxResults(page.getEveryPage());
		return q;
	}

	
	/**
	 * 执行count查询获得本次Hql查询所能获得的对象总数.
	 * 
	 * 本函数只能自动处理简单的hql语句,复杂的hql查询请另行编写count语句查询.
	 */
	protected long countHqlResult(final String hql, final Object... values) {
		String fromHql = hql;
		//select子句与order by子句会影响count查询,进行简单的排除.
		fromHql = "from " + StringUtils.substringAfter(fromHql, "from");
		fromHql = StringUtils.substringBefore(fromHql, "order by");

		String countHql = "select count(*) " + fromHql;

		try {
			Long count = findUnique(countHql, values);
			return count;
		} catch (Exception e) {
			throw new RuntimeException("hql can't be auto count, hql is:" + countHql, e);
		}
	}

	/**
	 * 执行count查询获得本次Hql查询所能获得的对象总数.
	 * 
	 * 本函数只能自动处理简单的hql语句,复杂的hql查询请另行编写count语句查询.
	 */
	protected long countHqlResult(final String hql, final Map<String, ?> values) {
		String fromHql = hql;
		//select子句与order by子句会影响count查询,进行简单的排除.
		fromHql = "from " + StringUtils.substringAfter(fromHql, "from");
		fromHql = StringUtils.substringBefore(fromHql, "order by");

		String countHql = "select count(*) " + fromHql;

		try {
			Long count = findUnique(countHql, values);
			return count;
		} catch (Exception e) {
			throw new RuntimeException("hql can't be auto count, hql is:" + countHql, e);
		}
	}

	/**
	 * 执行count查询获得本次Criteria查询所能获得的对象总数.
	 */
	@SuppressWarnings("unchecked")
	protected int countCriteriaResult(final Criteria c) {
		CriteriaImpl impl = (CriteriaImpl) c;

		// 先把Projection、ResultTransformer、OrderBy取出来,清空三者后再执行Count操作
		Projection projection = impl.getProjection();
		ResultTransformer transformer = impl.getResultTransformer();

		List<CriteriaImpl.OrderEntry> orderEntries = null;
		try {
			orderEntries = (List) ReflectionUtils.getFieldValue(impl, "orderEntries");
			ReflectionUtils.setFieldValue(impl, "orderEntries", new ArrayList());
		} catch (Exception e) {
			logger.error("不可能抛出的异常:{}", e.getMessage());
		}

		// 执行Count查询
		int totalCount = (Integer) c.setProjection(Projections.rowCount()).uniqueResult();

		// 将之前的Projection,ResultTransformer和OrderBy条件重新设回去
		c.setProjection(projection);

		if (projection == null) {
			c.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
		}
		if (transformer != null) {
			c.setResultTransformer(transformer);
		}
		try {
			ReflectionUtils.setFieldValue(impl, "orderEntries", orderEntries);
		} catch (Exception e) {
			logger.error("不可能抛出的异常:{}", e.getMessage());
		}

		return totalCount;
	}

	//-- 属性过滤条件(PropertyFilter)查询函数 --//

	/**
	 * 按属性查找对象列表,支持多种匹配方式.
	 * 
	 * @param matchType 匹配方式,目前支持的取值见PropertyFilter的MatcheType enum.
	 */
	public List<T> findBy(final String propertyName, final Object value, final MatchType matchType) {
		Criterion criterion = buildPropertyFilterCriterion(propertyName, value, matchType);
		return find(criterion);
	}

	/**
	 * 按属性过滤条件列表查找对象列表.
	 */
	public List<T> find(List<PropertyFilter> filters) {
		Criterion[] criterions = buildPropertyFilterCriterions(filters);
		return find(criterions);
	}



	/**
	 * 按属性条件列表创建Criterion数组,辅助函数.
	 */
	protected Criterion[] buildPropertyFilterCriterions(final List<PropertyFilter> filters) {
		List<Criterion> criterionList = new ArrayList<Criterion>();
		for (PropertyFilter filter : filters) {
			if (!filter.isMultiProperty()) { //只有一个属性需要比较的情况.
				Criterion criterion = buildPropertyFilterCriterion(filter.getPropertyName(), filter.getPropertyValue(),
						filter.getMatchType());
				criterionList.add(criterion);
			} else {//包含多个属性需要比较的情况,进行or处理.
				Disjunction disjunction = Restrictions.disjunction();
				for (String param : filter.getPropertyNames()) {
					Criterion criterion = buildPropertyFilterCriterion(param, filter.getPropertyValue(), filter
							.getMatchType());
					disjunction.add(criterion);
				}
				criterionList.add(disjunction);
			}
		}
		return criterionList.toArray(new Criterion[criterionList.size()]);
	}

	/**
	 * 按属性条件参数创建Criterion,辅助函数.
	 */
	protected Criterion buildPropertyFilterCriterion(final String propertyName, final Object propertyValue,
			final MatchType matchType) {
		Assert.hasText(propertyName, "propertyName不能为空");
		Criterion criterion = null;
		try {

			//根据MatchType构造criterion
			if (MatchType.EQ.equals(matchType)) {
				criterion = Restrictions.eq(propertyName, propertyValue);
			} else if (MatchType.LIKE.equals(matchType)) {
				criterion = Restrictions.like(propertyName, (String) propertyValue, MatchMode.ANYWHERE);
			} else if (MatchType.LE.equals(matchType)) {
				criterion = Restrictions.le(propertyName, propertyValue);
			} else if (MatchType.LT.equals(matchType)) {
				criterion = Restrictions.lt(propertyName, propertyValue);
			} else if (MatchType.GE.equals(matchType)) {
				criterion = Restrictions.ge(propertyName, propertyValue);
			} else if (MatchType.GT.equals(matchType)) {
				criterion = Restrictions.gt(propertyName, propertyValue);
			}
		} catch (Exception e) {
			throw ReflectionUtils.convertReflectionExceptionToUnchecked(e);
		}
		return criterion;
	}

	/**
	 * 判断对象的属性值在数据库内是否唯一.
	 * 
	 * 在修改对象的情景下,如果属性新修改的值(value)等于属性原来的值(orgValue)则不作比较.
	 */
	public boolean isPropertyUnique(final String propertyName, final Object newValue, final Object oldValue) {
		if (newValue == null || newValue.equals(oldValue)) {
			return true;
		}
		Object object = findUniqueBy(propertyName, newValue);
		return (object == null);
	}
	/*****
	 * 原始SQL分页程序，返回对象集合 ,主要针对的是原始SQL语句 返回分页集合
	 * 使用QueryRunner 方式来处理
	 * @param sql   SQL语句
	 * @param args   参数值
	 * @param entityClass 实体
	 * @param type 数据库类型 0：oracle,1:mysql
	 * @return
	 * @throws Exception
	 */

	public Page findBySqlResult(String sql,Page page,Object[] args,Class<?> entityClass,int type) {
		Page result = null;
		try {
			int totalRecords =findBySQLCount(sql,args);
			if (totalRecords == 0)
				logger.error("BaseDao.class -->findBySqlResult() is not data");
			else {
				
				
				result= PageUtil.createPage(page, totalRecords);
				int m = result.getBeginIndex();
				int n = m + result.getEveryPage();
				if(type==1) sql += "  limit "+m+ ", " +n;
				else{
					sql="select * from ( select row_.*, rownum rownum_ from ( " + sql
					+ " ) row_ where rownum <= " + n + ") where rownum_ > " + m;
				}
				List<?> list = this.findBySqlList(sql, args, entityClass);
				result.setResult(list);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}
	/****
	 * 查询数据总条数,主要针对的是原始SQL语句
	 * @param sql  sql 语句
	 * @param args  参数值，如果无，则传 null
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("deprecation")
	public  int findBySQLCount(String sql,Object[] args) {
		int r=0;
		Session session=this.sessionFactory.openSession();
		Connection con=null;
		QueryRunner qr=new QueryRunner();
		try {
			String count_sql="select count(*) from ("+sql+") t";
			log.info("sql:"+count_sql);
			con = session.connection();
			if(args!=null&&args.length>0){
				r=(Integer) qr.query(con, count_sql,args, new ResultSetHandler(){
					public Object handle(ResultSet rs) throws SQLException {
						rs.next();
						return rs.getInt(1);
					}});		
			}else{
				r=(Integer) qr.query(con, count_sql, new ResultSetHandler(){
				public Object handle(ResultSet rs) throws SQLException {
					rs.next();
					return rs.getInt(1);
				}});
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
				try {
					if(con!=null)
					con.close();
					session.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			
		}
		return r;
	}
	/******
	 *  原始SQL分页程序，返回对象集合 ,主要针对的是原始SQL语句 返回LIST 集合
	 *  使用QueryRunner 方式来处理
	 * @param sql   SQL语句
	 * @param args   参数值
	 * @param entityClass 实体
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public List<?> findBySqlList(String sql,Object[] args,Class<?> entityClass){
		Session session=this.sessionFactory.openSession();
		Connection conn=null;
		List<?> list =null;
		try {
			log.info("sql:"+sql);
			QueryRunner qRun=new QueryRunner();
			conn = session.connection();
			if(args!=null)
				list = (List<?>)qRun.query(conn, sql,args, new BeanListHandler(entityClass));
			else
				list = (List<?>)qRun.query(conn, sql, new BeanListHandler(entityClass));
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try {
				 if(conn!=null)	conn.close();
				 session.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	/****
	 * SQL语句执行，返回对象
	 * 使用QueryRunner 方式来处理
	 * @param sql
	 * @param args
	 * @param entityClass
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public Object findEntityBySql(String sql,Object[] args,Class<?> entityClass){
		Session session=this.sessionFactory.openSession();
		Connection conn=null;
		Object obj=null;
		try {
			log.info("sql:"+sql);
			QueryRunner qRun=new QueryRunner();
			conn = session.connection();
			
			if(args!=null)
				obj = qRun.query(conn, sql,args, new BeanHandler(entityClass));
			else
				obj = qRun.query(conn, sql, new BeanHandler(entityClass));
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try {
				 if(conn!=null)	conn.close();
				 session.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return obj;
	}
	/****
	 * 执行SQL/HQL语句
	 * @param sql   SQL语句
	 * @param values  参数
	 * @param type    0:HQL,1:sql
	 * @return
	 */
	public boolean ExcuteBySql(String sql,Object[] values,int type){
		Session session=this.sessionFactory.openSession();
		Transaction tx=null;
		try{
			tx=session.beginTransaction();
			Query query=null;
			if(type==0) query=session.createQuery(sql);
			if(type==1) query=session.createSQLQuery(sql);
			for (int i = 0; i < values.length; i++) {
				query.setParameter(i, values[i]);
			}
			query.executeUpdate();
			tx.commit();
			return true;
		}catch(HibernateException ex){
			if(tx!=null) tx.rollback();
			ex.printStackTrace();
		}finally{
			session.close();
		}
		return false;
	}
	
}
